package com.ruge.test.数据库的锁;

import com.ruge.test.commons.dbutils.SysUser;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;

/**
 * 爱丽丝、如歌  创建于 2018/7/25 13:32
 * 说明:  TODO
 */
public class Oracle {
    @Test
    public void test1() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.162:1521:entegor", "nxidealtest", "ideal");
        QueryRunner runner = new QueryRunner();
        List<LockModel> query = runner.query(conn, "SELECT s.sid, s.serial# as serial, s.username, s.schemaname, s.osuser, s.process, s.machine, s.terminal, s.logon_time, l.type FROM v$session s, v$lock l WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid", new BeanListHandler<LockModel>(LockModel.class));
        for (LockModel s : query
                ) {
            conn.setAutoCommit(false);
            Object[] params = {s.getSid(), s.getSerial()};
            String sql = "alter system kill session "+"'"+s.getSid()+","+s.getSerial()+"' immediate";
            System.out.println( sql);
            runner.update(conn, sql);
            conn.commit();
        }
        DbUtils.close(conn);
    }
}
